package com.o2o.cleaning.month.platform.ebusiness_plat.kuaishou

import org.apache.spark.sql.SparkSession

object CheckDataDetail {
  def main(args: Array[String]): Unit = {
    val spark = SparkSession.builder()
      .appName("KuaiShou_LiveStreaming")
      .config("spark.debug.maxToStringFields", "2000")
      .config("spark.serializer", "org.apache.spark.serializer.KryoSerializer")
      .config("spark.sql.caseSensitive", "true")
      .config("es.nodes", "192.168.1.29")
      .config("es.port", "9200")
      .config("cluster.name", "O2OElastic")
      .config("es.net.http.auth.user", "elastic")
      .config("es.net.http.auth.pass", "changeme")
      .master("local[*]")
      .getOrCreate()

    val sc = spark.sparkContext
    sc.hadoopConfiguration.set("fs.s3a.access.key", "GAO7EO9FWKPJ8WFCQDME")
    sc.hadoopConfiguration.set("fs.s3a.secret.key", "LZ0xaHBSYKHaJ9ECDbX9f7zin79UZkXfGoNapRPL")
    sc.hadoopConfiguration.set("fs.s3a.endpoint", "https://obs.cn-north-1.myhuaweicloud.com")
    sc.setLogLevel("ERROR")
    val year = 2022
    val month = 11
    val collection = s"kuaishou_webcast_shop_list_2108"
    val result_path = s"s3a://o2o-dataproces-group/zyf/livestreaming/kuaishou/resultData/${year}/${month}/${collection}/"
    val source_path = s"s3a://o2o-dataproces-group/zyf/livestreaming/kuaishou/sourceData/${year}/${month}/${collection}/"
    val address_path8 = s"s3a://o2o-dimension-table/address_table/address_table_2021/8/address_platform/kuaishou_address_2021_8_bak/"
    val address_path9 = s"s3a://o2o-dimension-table/address_table/address_table_2021/12/address_platform/kuaishou_address_2021_12/"

    //    spark.read.json("D:\\o2o4zyfBywork\\需求\\快手直播\\补店铺").registerTempTable("t1")
    //    spark.sql(
    //      """
    //        |select
    //        |_id as shop_id,
    //        |shopName as shopname,
    //        |concat('https://app.kwaixiaodian.com/merchant/shop/list?id=',_id) as shopurl,
    //        |shopLogo as shopimages,
    //        |'' as shoptype,
    //        |false as is_flagship,
    //        |'' as flagship_type,
    //        |false as is_oversea,
    //        |'20220314' as dt
    //        |from t1
    //        |""".stripMargin)
    //      .show(false)
    //      .repartition(1).write.option("header", true).csv("D:\\o2o4zyfBywork\\需求\\快手直播\\补店铺\\test")

    //    spark.read.orc(s"s3a://dws-data/g_data/2022/10/kuaishou/")
    //    spark.read.orc(s"s3a://o2o-tempdata/zyf/livestreaming/kuaishou/es/${year}/${month}/*")
//    spark.read.orc(s"s3a://o2o-tempdata/zyf/livestreaming/kuaishou/dws/2022/11/*")
          spark.read.orc(s"s3a://o2o-tempdata/zyf/livestreaming/kuaishou/es/2022/11/*")
      //        spark.read.option("header", true).csv(s"s3a://o2o-tempdata/zyf/livestreaming/kuaishou/categoryData/2021_9/")
      //      .show(false)
      //      .printSchema()
      .registerTempTable("t1")
    spark.sql(
      """
        |select count(1),sum(sellCount),sum(salesAmount) from t1
        |""".stripMargin).show(false)
    //          .repartition(30).write.orc(s"s3a://o2o-dataproces-group/zyf/livestreaming/kuaishou/sourceData/2022/4/kuaishou_categoryid_2204/")
    //    spark.read.json("s3a://o2o-dimension-table/address_table/address_table_2022/11/address_platform/kuaishou_address_2022_11/").registerTempTable("t1")
    //    spark.sql(
    //      """
    //        |select lastcategoryid,count(1) con from t1 group by lastcategoryid order by con desc
    //        |""".stripMargin).show(false)
    //    spark.read.json(s"s3a://o2o-dataproces-group/zyf/test/6/").registerTempTable("t1")
    //    spark.read.json(s"s3a://o2o-dimension-table/address_table/address_table_2022/1/address_platform/kuaishou_address_2022_1/")
    //      .show(10)
    //      .registerTempTable("t1")
    //    spark.read.option("header", true).csv(s"s3a://o2o-dataproces-group/zyf/test/5/").registerTempTable("t1")
    //    spark.sql(
    //      """
    //        |select * from (select shopId,count(1) con from t1 group by shopId) where con >1
    //        |""".stripMargin).show(false)
    //    import org.elasticsearch.spark._
    //    val index = s"2021_kuaishou/kuaishou_2021_8"
    //    val sourceDate = sc.esJsonRDD(s"${index}", "").values
    //    spark.read.json(sourceDate).registerTempTable("t1")
    //    spark.sql(
    //      """
    //        |select firstCategoryId,count(1) from t1 group by firstCategoryId
    //        |""".stripMargin)

    //
    //    spark.read.orc(result_path)
    //      .registerTempTable("data_table")
    //
    //
    //    spark.sql(
    //      """
    //        |select *,cast(priceText_new * sellCount as double) salesAmount_new from
    //        |(select *
    //        |,case
    //        |when cast(priceText as bigint) = 999999 then 9.9
    //        |when cast(priceText as bigint) = 99988 then 9.9
    //        |when cast( priceText as bigint) = 9988 then 9.9
    //        |when cast( priceText as bigint) = 99979 then 9.9
    //        |when cast( priceText as bigint) = 88888 then 9.9
    //        |when cast( priceText as bigint) = 8888 then 9.9
    //        |when (cast( priceText as bigint) >= 99990 and cast( priceText as bigint) <= 99999 )then 9.9
    //        |when (cast( priceText as bigint) >= 9990 and cast( priceText as bigint) <= 9999 )then 9.9
    //        |when (cast( priceText as bigint) >= 990 and cast( priceText as bigint) <= 999 )then 9.9
    //        |else priceText end priceText_new
    //        |from data_table where subplatformName = 'xiaodian')
    //        |""".stripMargin)
    //      .drop("priceText")
    //      .drop("salesAmount")
    //      .withColumnRenamed("priceText_new", "priceText")
    //      .withColumnRenamed("salesAmount_new", "salesAmount")

    //      .show(false)
    //      .registerTempTable("mid")
    //    spark.sql(
    //      """
    //        |select * from mid
    //        |""".stripMargin) //.show(false)
    //      .repartition(1).write.orc(s"s3a://o2o-dataproces-group/zyf/livestreaming/kuaishou/resultData/${year}/${month}_new/${collection}/")


    //    spark.read.orc(address_path).registerTempTable("addr_table")
    //              .show(false)
    //    spark.sql("select t1.good_id,,t1.sellcount,t1.salesamount,t2.province,t2.city,t2.county as district from data_table t1 " +
    //      "left join addr_table t2 " +
    //      "on t1.user_id = t2.shopId ")
    //      .write.orc(s"s3a://dws-data/split/split_data/${year}/kuaishou/${month}/")

    //    spark.sql("select good_id" +
    //      ",sellCount,salesAmount,youzan_source.province " +
    //      "from data_table ")
    //      .registerTempTable("data")

    //      .show(false)
    //    spark.sql("select * from data_table where good_id = 'TzQr9FVJYs4'").write.json("D://test_all")
    //    spark.sql(
    //      """
    //        |select count(1) count, cast(sum(sellCount) as bigInt) sell,cast(sum(salesAmount) as decimal(20,2)) sale from data_table where sellCount > 0
    //          """.stripMargin
    //    ).show(false)
    //    spark.sql(
    //      """
    //        |select * from
    //        |data_table
    //        |where
    //        |good_id = 1062537650903
    //        |""".stripMargin).show(true)

    //        spark.sql(
    //          """
    //            |select
    //            |product_id,good_id,title,priceText,sellcount,salesamount,shopId,goodUrl,is_flagship,original_cost,is_Presale,timestamp
    //            |from
    //            |data_table
    //            |where sellcount > 0 AND salesamount > 0
    //            |order by priceText desc
    //            |""".stripMargin).repartition(1).write.option("header", true).csv(s"s3a://o2o-dataproces-group/zyf/livestreaming/kuaishou/resultData/${year}/${month}/all/")


    //    spark.sql(
    //      """
    //        |select
    //        |good_id,title,goodUrl,priceText,sellcount,salesamount
    //        |from
    //        |data_table
    //        |where sellcount > 0 AND salesamount > 0
    //        |order by priceText desc
    //        |""".stripMargin).repartition(1).write.option("header", true).csv(s"D://test_kuaishou")

    //        spark.sql(
    //      """
    //        |select
    //        |good_id,title,goodUrl,priceText,sellcount,salesamount
    //        |from
    //        |data_table
    //        |where sellcount > 0 AND salesamount > 0
    //        |order by salesamount desc
    //        |limit 1000
    //        |""".stripMargin).write.option("header", true).csv(s"s3a://o2o-dataproces-group/zyf/livestreaming/kuaishou/resultData/${year}/${month}/sale_top1000/")
    //    spark.sql(
    //      """
    //        |SELECT  COUNT(1), SUM(sellCount) , SUM(salesamount)
    //        |FROM data_table
    //        |WHERE sellcount > 0 AND salesamount > 0
    //        |""".stripMargin).show(false)
    //    sc.sql(
    //      """
    //        |SELECT SUM(priceText_room) , SUM(sellCount_room) , SUM(priceText_room * sellCount_room) , COUNT(1)
    //        |FROM data_table
    //        |WHERE priceText_room > 0 AND sellCount_room > 0 AND priceText_room < 15000
    //        |""".stripMargin).show(false)
    //
    //    sc.sql(
    //      """
    //        |SELECT good_id ,  shopName ,  shopId , title , goods_source ,
    //        |SUM(priceText_room) AS priceText , SUM(sellCount_room) AS sellCount ,  SUM(priceText_room * sellCount_room) AS salesAmount
    //        |FROM data_table
    //        |WHERE sellCount_room > 0 AND  priceText_room > 0
    //        |GROUP BY shopName , good_id , shopId ,  title , goods_source
    //        |ORDER BY SUM(priceText_room * sellCount_room) DESC
    //        |LIMIT 1000
    //        |""".stripMargin).write.option("header", true).csv(s"s3a://o2o-dataproces-group/tian_yaxin/douyin/douyin_625/douyin_p_s/")
    //
    //    sc.sql(
    //      """
    //        |SELECT good_id ,  shopName ,  shopId , title , goods_source ,
    //        |SUM(priceText_room) AS priceText , SUM(sellCount_room) AS sellCount ,  SUM(priceText_room * sellCount_room) AS salesAmount
    //        |FROM data_table
    //        |WHERE sellCount_room > 0 AND  priceText_room > 0 AND priceText_room < 15000
    //        |GROUP BY shopName , good_id , shopId ,  title , goods_source
    //        |ORDER BY SUM(sellCount_room) DESC
    //        |LIMIT 1000
    //        |""".stripMargin).write.option("header", true).csv(s"s3a://o2o-dataproces-group/tian_yaxin/douyin/douyin_625/douyin_s_15000/")
    //
    //    sc.sql(
    //      """
    //        |SELECT good_id ,  shopName ,  shopId , title , goods_source ,
    //        |SUM(priceText_room) AS priceText , SUM(sellCount_room) AS sellCount ,  SUM(priceText_room * sellCount_room) AS salesAmount
    //        |FROM data_table
    //        |WHERE sellCount_room > 0 AND  priceText_room > 0 AND priceText_room < 15000
    //        |GROUP BY shopName , good_id , shopId ,  title , goods_source
    //        |ORDER BY SUM(priceText_room * sellCount_room) DESC
    //        |LIMIT 1000
    //        |""".stripMargin).write.option("header", true).csv(s"s3a://o2o-dataproces-group/tian_yaxin/douyin/douyin_625/douyin_p_s_15000/")
  }
}
